import json
import pymysql
import requests

url = "https://www.zhipin.com/wapi/zpgeek/search/joblist.json?scene=1&query=%E6%95%B0%E6%8D%AE&city=101230200&experience=&payType=&partTime=&degree=&industry=&scale=&stage=&position=&jobType=&salary=&multiBusinessDistrict=&multiSubway=&page=11&pageSize=30"

cookie = "lastCity=101230200; ab_guid=7879f0f8-9dcf-4d37-aa54-3cda6e52edc3; __g=-; __l=l=%2Fwww.zhipin.com%2Fweb%2Fgeek%2Fjob-recommend&r=&g=&s=3&friend_source=0; Hm_lvt_194df3105ad7148dcf2b98a91b5e727a=1735220717,1735288112,1735652662,1735803943; HMACCOUNT=3FE21B53C960261A; Hm_lpvt_194df3105ad7148dcf2b98a91b5e727a=1735803948; __zp_stoken__=4ddafw4o3wrUXOgtpZmgYClJ6ZGpyEsOJZ23CiMOFU2fDhXB2YMKwwqlewrxtw4bDgsKnwrDCk0zCpcKtwpBUwrLDhcKawp7DgmjCrcOHwrBkwp7EnMOywqXCjhbCkcOFwqk%2FNAwXDQ4NDRYMDwzCgcKCFRYVDBcNDg0UDxUWFTswwo5JPDw6PypWV0wRVmdlVWRKEVtUUDw7GQsYDDs4RT88P8OHw4XCuyzDhsK%2Bw4kvw4XCv8K6DT9EPz7DhjExMcK9wrAuGAvCvsKaC8K8wqVbw4XDmGHDj2dLwoc%2FwrwvMT0%2Bwr%2FEvzo8Gkk9RkY%2FPEZGPCo8OcOVZlLChz3DhiQvPx0%2BPD9FO0Y8P0M9PDA%2FRCM0PDwvSQ4MFxkKN0nDgMOHwr7DnTw%2F; __c=1735803939; __a=45056453.1730771147.1735652657.1735803939.146.21.9.146"

user_agent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36"

header = {
    "cookie": cookie,
    "user-agent": user_agent
}

resp = requests.get(url, headers=header)

result_list = []
if resp.status_code == 200:
    data = json.loads(resp.text)
    # 假设返回的JSON数据结构中，有一个键对应的值是数组，例如键名为 'result_array'
    result_list = data['zpData']['jobList']
else:
    print(f"请求失败，状态码: {resp.status_code}")


# 连接数据库
try:
    connection = pymysql.connect(
        host='localhost',  # 数据库主机地址，根据实际情况修改
        user='root',  # 数据库用户名，根据实际情况修改
        password='123456',  # 数据库密码，根据实际情况修改
        database='zhipin_jobs',  # 数据库名称，根据实际情况修改
        charset='utf8mb4'  # 设置字符集，支持存储中文等更多字符
    )

    try:
        with connection.cursor() as cursor:
            for data in result_list:
                print(data)
                # 构建插入语句，注意要根据表结构和字段顺序来准确书写
                sql = """
                INSERT INTO joblist (
                    securityId,
                    bossAvatar,
                    bossCert,
                    encryptBossId,
                    bossName,
                    bossTitle,
                    goldHunter,
                    bossOnline,
                    encryptJobId,
                    expectId,
                    jobName,
                    lid,
                    salaryDesc,
                    jobLabels,
                    jobValidStatus,
                    iconWord,
                    skills,
                    jobExperience,
                    daysPerWeekDesc,
                    leastMonthDesc,
                    jobDegree,
                    cityName,
                    areaDistrict,
                    businessDistrict,
                    jobType,
                    proxyJob,
                    proxyType,
                    anonymous,
                    outland,
                    optimal,
                    iconFlagList,
                    itemId,
                    city,
                    isShield,
                    atsDirectPost,
                    gps,
                    encryptBrandId,
                    brandName,
                    brandLogo,
                    brandStageName,
                    brandIndustry,
                    brandScaleName,
                    welfareList,
                    industry,
                    contact,
                    showTopPosition
                ) VALUES (
                    %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
                )
                """
                # 将字典中的值提取为元组，用于插入语句的值部分，对于JSON类型字段（如列表等）使用json.dumps转换为字符串
                values = (
                    data["securityId"],
                    data["bossAvatar"],
                    data["bossCert"],
                    data["encryptBossId"],
                    data["bossName"],
                    data["bossTitle"],
                    data["goldHunter"],
                    data["bossOnline"],
                    data["encryptJobId"],
                    data["expectId"],
                    data["jobName"],
                    data["lid"],
                    data["salaryDesc"],
                    str(data["jobLabels"]),
                    data["jobValidStatus"],
                    data["iconWord"],
                    str(data["skills"]),
                    data["jobExperience"],
                    data["daysPerWeekDesc"],
                    data["leastMonthDesc"],
                    data["jobDegree"],
                    data["cityName"],
                    data["areaDistrict"],
                    data["businessDistrict"],
                    data["jobType"],
                    data["proxyJob"],
                    data["proxyType"],
                    data["anonymous"],
                    data["outland"],
                    data["optimal"],
                    str(data["iconFlagList"]),
                    data["itemId"],
                    data["city"],
                    data["isShield"],
                    data["atsDirectPost"],
                    str(data["gps"]),
                    data["encryptBrandId"],
                    data["brandName"],
                    data["brandLogo"],
                    data["brandStageName"],
                    data["brandIndustry"],
                    data["brandScaleName"],
                    str(data["welfareList"]),
                    data["industry"],
                    data["contact"],
                    data["showTopPosition"]
                )
                # 执行插入语句
                cursor.execute(sql, values)
            connection.commit()
    finally:
        connection.close()
except pymysql.MySQLError as e:
    print(f"数据库操作出错: {e}")
